package com.sinosoft.hydra.um.services.component.dataCollect.readers;

import cn.com.sinux.spring.commons.SystemConfig;
import cn.com.sinux.spring.exception.BusinessException;
import cn.com.sinux.spring.exception.ServiceException;
import cn.com.sinux.spring.utils.FileUtils;
import cn.com.sinux.spring.utils.UploadUtils;
import com.artofsolving.jodconverter.DefaultDocumentFormatRegistry;
import com.artofsolving.jodconverter.DocumentConverter;
import com.artofsolving.jodconverter.DocumentFormat;
import com.artofsolving.jodconverter.openoffice.connection.OpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.connection.SocketOpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.converter.OpenOfficeDocumentConverter;
import com.sinosoft.hydra.logging.LogModules;
import com.sinosoft.hydra.um.domain.model.LabDataParser;
import com.sinosoft.hydra.um.services.component.dataCollect.DataParser;
import com.sinosoft.hydra.um.services.component.dataCollect.DataReader;
import com.sinosoft.hydra.um.services.component.dataCollect.DataResult;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * Created by lcb on 2016/7/7.
 */
public class ExcelReader implements DataReader {

    private Workbook getWorkBook(String path, int total) {
        InputStream in = null;
        Workbook workbook;
        try {
            in = new FileInputStream(path);
            if ("xls".equals(FileUtils.getExtName(path))) {
                workbook = new HSSFWorkbook(new POIFSFileSystem(in));
            } else if ("xlsx".equals(FileUtils.getExtName(path))) {
                workbook = new XSSFWorkbook(in);
            } else {
                LoggerFactory.getLogger(LogModules.STD_LOGGER).error("文件的扩展名不正确，不能正确解析excel");
                return null;
            }
            return workbook;
        } catch (FileNotFoundException e) {
            LoggerFactory.getLogger(LogModules.STD_LOGGER).error("文件路径{}未找到", path);
            return null;
        } catch (IOException e) {
            LoggerFactory.getLogger(LogModules.STD_LOGGER).error("excel文件解析失败，没有获取到数据{}", e.getMessage());
            return null;
        } catch (Exception ex) {
            if (total == 0) {
                //尝试另存为以后再次处理
                String _path = path.substring(0, path.lastIndexOf("."));
                int i = 0;
                String _p;
                String extName = FileUtils.getExtName(path);
                do {
                    _p = _path + i + "." + extName;
                    i++;
                } while (new File(_p).exists());
                saveOtherExcel(SystemConfig.getProperty("openOffice.home"), SystemConfig.getIntegerProperty("openOffice.server.port"),
                        path, _p);
                total++;
                return getWorkBook(_p, total);
            } else {
                LoggerFactory.getLogger(LogModules.STD_LOGGER).error("其他原因导致的解析错误{}", ex.getMessage());
                throw new BusinessException("excel文件异常导致的解析错误，请打开excel另存为新的文件再次上传提交");
            }
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    LoggerFactory.getLogger(LogModules.STD_LOGGER).error("输入流关闭失败{}", e.getMessage());
                }
            }
        }
    }

    private boolean doConnect(OpenOfficeConnection connection) {
        if (connection.isConnected()) {
            return true;
        }
        int i = 0;
        boolean connected = false;
        while (i < 3 && !connected) {
            try {
                if (i > 0) {
                    LoggerFactory.getLogger(LogModules.STD_LOGGER).debug("再次连接openOffice，已连接次数{}", i + 1);
                }
                connection.connect();
                LoggerFactory.getLogger(LogModules.STD_LOGGER).debug("连接openOffice成功");
                connected = connection.isConnected();
            } catch (Exception ex) {
                i++;
                System.out.print(ex.getMessage());
                LoggerFactory.getLogger(LogModules.STD_LOGGER).debug("连接openOffice失败，失败原因：{}", ex.getMessage());
                try {
                    Thread.sleep(3000);
                } catch (InterruptedException e) {
                    System.out.print(e.getMessage());
                }
            }
        }
        return connected;
    }

    private synchronized void saveOtherExcel(String openOffieHome, int port, String path, String destPath) {
        OpenOfficeConnection connection = null;
        Process proc = null;
        try {

            if (StringUtils.isBlank(openOffieHome)) {
                throw new ServiceException("没有配置正确的openoffice路径，转换失败！");
            }
            // 启动OpenOffice的服务
            String command = openOffieHome
                    + "/program/soffice.exe -headless -accept=\"socket,host=127.0.0.1,port=" + port + ";urp;\"";
            System.out.print(command);
            LoggerFactory.getLogger(LogModules.STD_LOGGER).debug("文件转换的命令{}", command);
            proc = Runtime.getRuntime().exec(command);
            connection = new SocketOpenOfficeConnection(
                    "127.0.0.1", port);
            System.out.print(proc);
            LoggerFactory.getLogger(LogModules.STD_LOGGER).debug("开始连接");
            if (!doConnect(connection)) {
                throw new ServiceException("没有能够有效启用openoffice，转换失败！");
            }
            LoggerFactory.getLogger(LogModules.STD_LOGGER).debug("连接成功，开始转换");
            DocumentConverter converter = new OpenOfficeDocumentConverter(connection);
            DocumentFormat formatReg = new DefaultDocumentFormatRegistry().getFormatByFileExtension("xls");

            File inputFile = new File(path);
            if (!inputFile.exists()) {
                LoggerFactory.getLogger(LogModules.STD_LOGGER).error("文件不存在，不能转换{}", path);
                return;
            }
            File dest = new File(destPath);
            DefaultDocumentFormatRegistry _formatReg = new DefaultDocumentFormatRegistry();
            converter.convert(inputFile, _formatReg.getFormatByFileExtension("xls"), dest, formatReg);
        } catch (Exception e) {
            e.printStackTrace();
            LoggerFactory.getLogger(LogModules.STD_LOGGER).error("文件转换出现错误{}", e.getMessage());
        } finally {
            if (connection != null && connection.isConnected()) {
                connection.disconnect();
                connection = null;
            }
            if (proc != null) {
                proc.destroy();
                proc = null;
            }
        }
    }


    public DataResult read(List<String> paths, Date begin, Date end, DataParser parser, LabDataParser labDataParser) {
        DataResult dataResult = new DataResult();
        for (String path : paths) {
            try {
                Workbook workbook = getWorkBook(path, 0);
                if (workbook != null) {
                    readExcel(workbook, begin, end, parser, dataResult, labDataParser);
                } else {
                    throw new BusinessException("读取资源出错，请检查数据文件");
                }
            } catch (Exception ex) {
                LoggerFactory.getLogger(LogModules.STD_LOGGER).error("其他原因导致的解析错误{}", ex.getMessage());
                throw new BusinessException("excel文件异常导致的解析错误，请打开excel另存为新的文件再次上传提交");
            }
        }
        return dataResult;
    }

    protected void readExcel(Workbook workbook, Date begin, Date end, DataParser parser, DataResult dataResult, LabDataParser labDataParser) {
        //循环每一页
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            if (sheet == null || sheet.getLastRowNum() == 0) {
                continue;
            }
            List<String> titles;
            if (labDataParser.getTitleRowNum() == -1) {
                titles = parser.getTitles();
            } else {
                titles = readCell(sheet.getRow(labDataParser.getTitleRowNum()), labDataParser);
            }
            int last = labDataParser.getCellsNum() == -1 ? titles.size() : labDataParser.getCellsNum();
            dataResult.addTitles(titles, last, parser);
            //循环每一行
            for (Row row : sheet) {
                if (row.getRowNum() >= labDataParser.getStartRowNum()) {
                    List<String> cells = readCell(row, labDataParser);
                    if (!parser.valid(cells)) {
                        break;
                    }
                    dataResult.addData(parser.parse(cells, begin, end), last, parser);
                }
            }
        }
    }

    protected List<String> readCell(Row row, LabDataParser labDataParser) {
        //循环每一个单元格
        List<String> cells = new ArrayList<String>();
        int last = labDataParser.getCellsNum() == -1 ? row.getLastCellNum() + 1 : labDataParser.getCellsNum();
        for (int i = 0; i < last; i++) {
            Cell cell = row.getCell(i);
            cells.add(parseString(getCellData(cell)));
        }
        return cells;
    }

    private String parseString(Object obj) {
        if (obj instanceof String) {
            return (String) obj;
        } else if (obj instanceof Double) {
            return String.valueOf(obj);
        } else if (obj instanceof Date) {
            return DateFormatUtils.format((Date) obj, "yyyy/MM/dd HH:mm:ss");
        } else if (obj instanceof Boolean) {
            return (Boolean) obj ? "true" : "false";
        }
        return "";
    }

    protected Object getCellData(Cell cell) {
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    return HSSFDateUtil.isCellDateFormatted(cell)
                            ? cell.getDateCellValue()
                            : cell.getNumericCellValue();
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    return cell.getStringCellValue();
                // 默认的Cell值
                case Cell.CELL_TYPE_BLANK:
                    return StringUtils.EMPTY;
                case Cell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue();
                default:
                    return null;
            }
        }
        return "";
    }

}
